package dao;

import domain.Student;
import util.DBUtil;
import util.Pager;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class StudentDAOImpl {
    //获取记录总数
    public int getTotalCount() {
        String sql = "select count(*) as num from student";
        try {
            // 创建QueryRunner对象
            ResultSet rs = DBUtil.executeQuery(sql, null);
            int count = 0;
            // 执行查询， 返回结果的第一行的第一列
            if (rs.next())
                count = rs.getInt("num");
            return count;

        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        }

    }

    //获取student表数据
    public void getStudents(Pager<Student> pb) {
        //2. 查询总记录数;  设置到pb对象中
        int totalCount = this.getTotalCount();
        pb.setTotalCount(totalCount);

        // 判断
        if (pb.getCurrentPage() <= 0) {
            pb.setCurrentPage(1);                        // 把当前页设置为1
        } else if (pb.getCurrentPage() > pb.getTotalPage()) {
            pb.setCurrentPage(pb.getTotalPage());        // 把当前页设置为最大页数
        }

        //1. 获取当前页： 计算查询的起始行、返回的行数
        int currentPage = pb.getCurrentPage();
        int index = (currentPage - 1) * pb.getPageCount();        // 查询的起始行
        int count = pb.getPageCount();                            // 查询返回的行数


        //3. 分页查询数据;  把查询到的数据设置到pb对象中
        String sql = "select * from student limit ?,?";

        try {
            // 得到Queryrunner对象
            Object[] params = {index, count};
            ResultSet rs = DBUtil.executeQuery(sql, params);
            // 根据当前页，查询当前页数据(一页数据)
            List pageData = new ArrayList();

            //循环显示rs对象中的数据;rs.next()遍历指针
            Student student;
            while (rs.next()) {
                //每一条记录，转变成一个Student对象
                student = new Student();
                student.setId(rs.getInt("id"));
                student.setName(rs.getString("name"));
                student.setPassword(rs.getString("password"));
                student.setSex(rs.getInt("sex"));
                student.setClazz(rs.getString("clazz"));
                student.setBirthday(rs.getString("birthday"));
                //把student对象加入students列表
                pageData.add(student);

            }
            // 设置到pb对象中
            pb.setPageData(pageData);

        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    //insert student
    public boolean addStudent(Student student) {
        String sql = "insert into student(name,password,sex,clazz,birthday) values (?,?,?,?,?)";
        Object[] params = {student.getName(), student.getPassword(), student.getSex(), student.getClazz(), student.getBirthday()};
        return DBUtil.executeUpdate(sql, params);
    }

    //get student
    public Student getStudent(int id) {
        String sql = "select * from student where id=?";
        Object[] params = {id};
        Student student = new Student();
        ResultSet rs = DBUtil.executeQuery(sql, params);
        try {
            if (rs.next()) {

                student.setId(rs.getInt("id"));
                student.setName(rs.getString("name"));
                student.setSex(rs.getInt("sex"));
                student.setPassword(rs.getString("password"));
                student.setClazz(rs.getString("clazz"));
                student.setBirthday(rs.getString("birthday"));

            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            return student;
        }
    }

    //update student
    public boolean updateStudent(Student student) {
        String sql = "update student set name=?,password=?,sex=?,clazz=?,birthday=? where id=?";
        Object[] params = {student.getName(), student.getPassword(), student.getSex(), student.getClazz(), student.getBirthday(), student.getId()};
        return DBUtil.executeUpdate(sql, params);

    }

    //delete student
    public boolean deleteStudent(int id) {
        String sql = "delete from student where id=?";
        Object[] params = {id};
        return DBUtil.executeUpdate(sql, params);
    }

}
